package chan.module.project.database.vastdata.bcg;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
 * by chen zhen qin
 * time 2025-1-2
 */
public class VastBaseG100NBCGOperate extends NBCGOperate implements Operate {
    private static final Logger logger = new Logger();
    /**
     * 创建触发器 多参公共方法
     *
     * @param tablename     需要创建触发器的表名
     * @param colNameList   需要监控的表字段列表
     * @param actiontype    需要创建的触发器类型（insert、update、delete）
     * @param servicename   服务名（暂时没用）
     * @param typename      类型名（暂时没用）
     * @param msgParamList  消息参数字段列表
     * @param serviceNameID 服务名ID
     * @param isCycle       是否为双向同步
     * @return
     * @throws OperateException
     * @author chen zhen qin
     * @datetime 2025-1-2
     */
    private static final String VAR_NEW_ROW = "NEW";
    private static final String VAR_OLD_ROW = "OLD";
    private static final String VAR_SEQ_NUMBER = "var_seq_number";
    private static final String VAR_SERVICE_ID = "var_service_id";

    @Override
    public TriggerResult addCycleTrigger(String tablename, String actiontype, String servicename, String typename, ArrayList al, String serviceNameID) throws OperateException {
        return buildTriggerResult(tablename, null, actiontype, servicename, typename, al, serviceNameID, true);
    }

    @Override
    public TriggerResult addCycleTrigger(String tablename, ArrayList colNameList, String actiontype, String servicename, String typename, ArrayList al, String serviceNameID) throws OperateException {
        return buildTriggerResult(tablename, colNameList, actiontype, servicename, typename, al, serviceNameID, true);
    }

    @Override
    public TriggerResult addTrigger(String tablename, String actiontype, String servicename, String typename, ArrayList al, String serviceNameID) throws OperateException {
        return buildTriggerResult(tablename, null, actiontype, servicename, typename, al, serviceNameID, false);
    }

    @Override
    public TriggerResult addTrigger(String tablename, ArrayList colNameList, String actiontype, String servicename, String typename, ArrayList al, String serviceNameID) throws OperateException {
        return buildTriggerResult(tablename, colNameList, actiontype, servicename, typename, al, serviceNameID, false);
    }

    public TriggerResult buildTriggerResult(String tablename, ArrayList colNameList, String actiontype, String servicename1, String typename,
                                            ArrayList msgParamList, String serviceNameID, boolean isCycle) throws OperateException {
        //参数都不可是null
        if (tablename == null || actiontype == null || msgParamList == null) {
            throw new OperateException("Parameters for adding triggers are invalid.");
        }

        /*
         * 创建触发器之前先处理配置数据
         */
        actiontype = actiontype.toLowerCase();
        //String triggername1 = "\"" + generateTriggerName(tablename, actiontype) + "\"";
        String triggername = generateTriggerName(tablename, actiontype);
        triggername = triggername.toUpperCase();
        int num;//有多少个参数
        if (msgParamList == null || (num = msgParamList.size() / 2) == 0) {
            throw new OperateException("The length of Parameter column to make certain one record is invalid.");
        }
        // 考虑到数字作为schema名称,或者数字作为table名称,所以要在trigger名和表名,schema名上面加引号
        int schemaIndex = tablename.lastIndexOf(".");
        if (schemaIndex >= 0) {
            tablename = "\"" + tablename.substring(0, schemaIndex) + "\".\"" + tablename.substring(schemaIndex + 1) + "\"";
        }
        String markOperateType;
        if (actiontype.equalsIgnoreCase("INSERT") || actiontype.equalsIgnoreCase("UPDATE")) {
            markOperateType = VAR_NEW_ROW;
        } else if (actiontype.equalsIgnoreCase("DELETE")) {
            markOperateType = VAR_OLD_ROW;
        } else {
            //未知的触发器的创建类型！
            throw new OperateException("The creation type of the unknown trigger！");
        }

        String funcName = "FUN_" + triggername;
        funcName = "\"" + funcName + "\"";
        triggername = "\"" + triggername + "\"";

        StringBuilder createFuncSql = new StringBuilder();
        createFuncSql.append("-- 开始-创建FUNCTION函数体\n");
        createFuncSql.append("CREATE OR REPLACE FUNCTION ").append(funcName).append("()").append("\n");
        createFuncSql.append("RETURNS TRIGGER AS \n");
        createFuncSql.append("'\n");
        createFuncSql.append("DECLARE \n");
        createFuncSql.append("\t").append(VAR_SERVICE_ID).append(" VARCHAR;\n");
        createFuncSql.append("\t").append(VAR_SEQ_NUMBER).append(" BIGINT;\n");
        createFuncSql.append("BEGIN\n");
        createFuncSql.append("\n");

        createFuncSql.append("-- 赋值ServiceID\n");
        createFuncSql.append(VAR_SERVICE_ID).append(" := ''").append(serviceNameID).append("'';\n");
        createFuncSql.append("\n");

        //是否属于创建双向同步，如果是，则加入判断SQL语句
        isCycle = isCycle && !StringUtil.isEmpty(this._usrname);
        if (isCycle) {
            createFuncSql.append("-- 开始-根据当前用户名来判断是否同步\n");
            createFuncSql.append("IF( \n");
            createFuncSql.append("\tCURRENT_USER <> ''").append(this._usrname).append("'' \n");
            createFuncSql.append(")THEN\n");
        }
        createFuncSql.append("\n");
        //是否有监控字段，如果有，加入监控字段列表SQL语句 监控字段
        // INSERT类型操作时候不能比较
        boolean hasMonitoringFields = !actiontype.equalsIgnoreCase("INSERT")
                && colNameList != null
                && !colNameList.isEmpty();
        if ( hasMonitoringFields ) {
            createFuncSql.append("-- 开始-根据监控字段判断是否同步\n");
            createFuncSql.append("IF(\n");
            for (int i = 0; i < colNameList.size(); i++) {
                String monCol = (String) colNameList.get(i);
                createFuncSql.append("\t").append(VAR_NEW_ROW).append(".").append(monCol)
                        .append(" <> ")
                        .append(VAR_OLD_ROW).append(".").append(monCol).append(" OR \n");
            }
            createFuncSql.setLength(createFuncSql.length() - " OR \n".length());
            createFuncSql.append("\n)THEN\n");
            createFuncSql.append("\n");
        }

        createFuncSql.append("-- 获取自增消息ID\n");
        createFuncSql.append("SELECT nextval(''").append("NBCG_MSG_SEQ").append("'') INTO ").append(VAR_SEQ_NUMBER).append(";\n");
        createFuncSql.append("-- 写入变更的值到触发器主表里面\n");
        createFuncSql.append("INSERT INTO NBCG_MSG (\n")
                .append("\tServiceID,\n")
                .append("\tMsgId,\n")
                .append("\tTableName,\n")
                .append("\tMsgQueuedAt,\n")
                .append("\tOperationType,\n");
        for (int i = 0; i < num; i++) {
            createFuncSql.append("\tMsgParam").append(i + 1).append(",\n");
        }
        createFuncSql.setLength( createFuncSql.length() - ",\n".length() );

        String[] tables = tablename.split("\\.");
        String schema = tables[0];
        String table = tables[1];
        String proTableName = "''" + schema + "." + table + "''";
        createFuncSql.append("\n)VALUES (\n");
        createFuncSql.append("\t").append(VAR_SERVICE_ID).append(",\n")
                .append("\t").append(VAR_SEQ_NUMBER).append(",\n")
                .append("\t").append(proTableName).append(",\n")
                .append("\t").append("CURRENT_TIMESTAMP,\n")
                .append("\t").append("''").append(actiontype).append("'',\n");

        //加入变更字段数据
        for (int i = 0; i < num; i++) {
            String sna = (String) msgParamList.get(i * 2);
            String sal = ((String) msgParamList.get(i * 2 + 1)).toUpperCase();
            createFuncSql.append("\t");
            switch (sal) {
                // 把时间类型转为时间格式的字符串类型
                case "DATE":
                case "TIME":
                case "TIMESTAMP":
                case "SMALLDATETIME":
                case "SECONDDATE": {
                    createFuncSql.append("TO_CHAR(").append(markOperateType).append(".").append(sna).append(",''YYYY-MM-DD HH24:MI:SS'')");
                    break;
                }
                default:
                    createFuncSql.append(markOperateType).append(".").append(sna);
            }
            createFuncSql.append(",\n");
        }
        createFuncSql.setLength( createFuncSql.length() - ",\n".length() );
        createFuncSql.append("\n");
        createFuncSql.append(");\n");

        if (hasMonitoringFields) {
            createFuncSql.append("END IF; -- 结束-根据监控字段判断是否同步 \n");
        }
        if (isCycle) {
            createFuncSql.append("END IF; -- 结束-根据当前用户名来判断是否同步 \n");
        }
        // 结束语句
        createFuncSql.append("\n");
        createFuncSql.append("RETURN ").append(markOperateType).append(";\n");
        createFuncSql.append("END;\n");
        createFuncSql.append("' LANGUAGE plpgsql;\n");
        createFuncSql.append("-- 结束-创建FUNCTION函数体\n");
        createFuncSql.append("\n");

        createFuncSql.append("-- 开始-创建行触发器\n");
        createFuncSql.append("CREATE TRIGGER ").append(triggername).append("\n");
        createFuncSql.append("AFTER ").append(actiontype.toUpperCase()).append(" ON ").append(tablename).append("\n");
        createFuncSql.append("FOR EACH ROW \n");
        createFuncSql.append("EXECUTE PROCEDURE ").append(funcName).append("();\n");
        createFuncSql.append("-- 结束-创建行触发器\n");

        String createTriggerSql = createFuncSql.toString();
        logger.debug("createTriggerSql: {}", createTriggerSql);
        TriggerResult result = new TriggerResult();
        result.setName(triggername);
        result.setSql(createTriggerSql);
        return result;
    }

    @Override
    protected boolean ifExitSameTriggerName(String triggerName) {
        Statement stm = null;
        ResultSet rs = null;
        boolean rsFlag = false;
        String firstC = triggerName.substring(0, 1);
        String lastC = triggerName.substring(triggerName.length() - 1);
        if (firstC.equals("\"") && lastC.equals("\"")) {
            triggerName = triggerName.substring(1, triggerName.length() - 1);
        }
        String selectSql = "SELECT TRIGGER_NAME FROM TRIGGERS WHERE TRIGGER_NAME  = '" + triggerName + "' LIMIT 1 ";
        try {
            stm = con.createStatement();
            rs = stm.executeQuery(selectSql);
            rsFlag = rs.next();
        } catch (SQLException e) {
            logger.error("Failed to get triggers info :" + e.getMessage());
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ignored) {
                }
            }
            if (stm != null) {
                try {
                    stm.close();
                } catch (SQLException ignored) {
                }
            }
        }
        return rsFlag;
    }

}
